#!/usr/bin/python
### This programs is for uploading the ring frags into the database, it checks if the ring fragment already exists and then loads new frags (Agata/Chido/Torsten).

## old scripts screwed up with tautomers (RB)
##introduced dictionary for compound upload -> easier to check for tautomers (RB)
## note: if one compound contains two times the same fragment both are currently uploaded (it might be that get_scaffolds does already delete the duplicates but I am not sure) (RB)


from openeye.oechem import *
import chemistry, operator, mysql

import string,os, sys, mysql, datetime
import MySQLdb

if len(sys.argv) <> 10:
      print 'mysql_load_templates.py  <username> <password> <data file>  <ringDB (table in which rings are stored)> <name of ring_id_field> <molDB (table in which molecules are stored)> <name of id field> <name of ring_smiles_field>  <initial>'
      sys.exit(1)

username 	= sys.argv[1]
password 	= sys.argv[2]
data_file 	= open(sys.argv[3], 'r')	# data file with fragment_smiles '/t' separated ID of compound
ringDB 		= sys.argv[4]			# table in which the rings are stored			(e.g. unique_ring_fragments_corrected)
ringID 		= sys.argv[5]			# column in ringDB for the ring id 			(e.g. ring_id_fg_u)
molDB 		= sys.argv[6]			# table in which the mols are stored			(e.g. unique_ring_in_mol_fg_corrected)
ID 		= sys.argv[7]			# column in molDB for molecular id			(e.g. id)
ringSMILES 	= sys.argv[8]			# column in ringDB which fragment smiles are stored	(e.g. ring_smiles_fg_u)
initial 	= sys.argv[9]			# cpm etc						(e.g. CPM)

utc_datetime = datetime.datetime.utcnow()
date  = str(utc_datetime.strftime("%Y-%m-%d"))

print ''




conn=mysql.connect2server(password, username,'purchasable')  			  	  
cursor = conn.cursor ()

#LOCK ringDB table, OTHERWISE PARALLEL UPLOADING WILL END IN CHAOS (RB)
command = "lock table " + ringDB + " write, " + molDB + " write"
print command
cursor.execute(command)

##########get the latest ring_id from ring_fragment table
command_max_id = "SELECT MAX( " + ringID + " ) FROM  " + ringDB 
cursor.execute(command_max_id)
result_max_id = cursor.fetchall()

if str(result_max_id) == '((None,),)':
  ring_id = 1
else:
  ring_id = int(str(result_max_id)[2:str(result_max_id).find('L')])+1

### read input file (smiles	id) into array
input_file_array = data_file.readlines()
#print len(input_file_array)

#convert input_file_array to dictionary
compound_dict = {} # = {identifier:[[smiles1], [smiles2], [[smiles3_taut1],[smiles3_taut2]],...}
for i in input_file_array:
	tautomer = False
	new_tautomer = False
	########split ith position in array
  	smiles, identification = string.split(i.strip(),'\t')
	#check if tautomer
	if not identification[0].isdigit():
		tautomer = True
		if identification[0] == 'A':
			new_tautomer = True
		identification = identification[1:]
		
	if not compound_dict.has_key(identification):
		compound_dict[identification] = [[smiles]]
	elif new_tautomer or not tautomer: #new tautomer or not a tautomer at all -> add smiles to litst
		compound_dict[identification].append([smiles])
		new_tautomer = False
	else: #tautomer of molecule that is already in dictionary -> append last smiles entry
		number_entries = len(compound_dict[identification])
		compound_dict[identification][number_entries-1].append(smiles)

#print compound_dict

for identifier in compound_dict:

##########delete compund from molDB table before compounds is uploaded 
	command = "DELETE FROM " + molDB + " WHERE  " + ID + " = " + identifier
        #print command
        cursor.execute(command)

	for smiles_list in compound_dict[identifier]:
		if len(smiles_list) == 1: #tautomers are always real fragments
		##########check: is smiles a real fragment 

 			mol = OEGraphMol()
			fragment = False
  			if (OEParseSmiles(mol, smiles_list[0]) ==1):
    				pat1 = OESubSearch()
    				pat1.Init('[R;*]!@*')
    				if pat1.SingleMatch(mol) == 1:
 					fragment = True    
    				else:
      					pat2 = OESubSearch()
      					pat2.Init('[*;R;n,N]')
      					if pat2.SingleMatch(mol) == 1:
						fragment = True
      					else:
         					continue #next smiles_list for current identifier 
		#real fragment, load
		exists = False
		for smiles in smiles_list: #check if fragment already exists
			command = "SELECT  " + ringID + " FROM " + ringDB + " WHERE " + ringSMILES + "   = '" + smiles + "' LIMIT 1"
      			cursor.execute(command)
      			result_fragments = cursor.fetchall()
			if len(result_fragments) > 0:
				exists = True
				old_ring_id = result_fragments[0][0]
				break #this loop can now be stopped
	
		if not exists: #load fragment, if tautomer -> load each tautomer 
			ring_id = ring_id +1
			old_ring_id = ring_id
			for smiles in smiles_list:
				command = "INSERT INTO " + ringDB + " (" + ringID + ", " + ringSMILES + ", date_of_update, updated_by) VALUES ('" + str(old_ring_id) + "', '" + smiles + "', '" + date + "', '" +  initial+"')"
				#print command
         			cursor.execute(command)
		#load compound
		command = "INSERT INTO " + molDB + "(" + ringID + ", " + ringSMILES + ", " + ID +  ") VALUES (" + str(old_ring_id) + ", '" +smiles+ "'," + identifier + ")"
      		#print command
      		cursor.execute(command)
			


conn.commit()

#unlock tables (RB)
command = "unlock tables"
cursor.execute(command)
cursor.close()

conn.close ()

